"""
@author: xiangping
@contact: xiangpingbu@gmail.com
@time: 2020/1/7 4:15 下午
@file: gaolu_dal
@Desc:
"""
import datetime
from typing import List

from sqlalchemy import and_

from app.database import convert
from app.database.convert import add_one
from app.exts import ironman_redis
from app.exts import ironman_redis as redis
from app.models.po.gaolu import gaolu_model
from app.models.po.gaolu.gaolu_banbie_score_po import GaoLuBanBieScorePO
from app.models.po.gaolu.gaolu_model_input_po import GaoLuModelInputPO
from app.models.po.gaolu.gaolu_model_input_simulate_po import GaoLuModelInputSimulatePO
from app.models.po.gaolu.gaolu_predict_po import GaoLuPredictPO
from app.schemas.gaolu import GaoLuSchema
from sqlalchemy import func, DECIMAL
import logging

gaolu_schema = GaoLuSchema()
gaolu_schemas = GaoLuSchema(many=True)


def gaolu_history_between(start, end, left='close', right='close', order_by=None) -> List[GaoLuPredictPO]:
    """
    :param start:
    :param end:
    :param left:
    :param right:
    :param order_by:
    :return:
    """
    if end is None or start is None:
        return None
    ret = gaolu_history_between_query(start, end, left, right, order_by=order_by).all()
    return ret


def gaolu_history_between_query(start, end, left='close', right='close', order_by=None) \
        -> List[GaoLuPredictPO]:
    if left == "close" and right == "close":
        ret = convert.query(GaoLuPredictPO, GaoLuPredictPO.GMT_CREATE.between(start, end), order_by=order_by)
    elif left == "open" and right == "open":
        ret = convert.query(GaoLuPredictPO,
                            and_(GaoLuPredictPO.GMT_CREATE > start, GaoLuPredictPO.GMT_CREATE < end), order_by=order_by)
    elif left == "open" and right == "close":
        ret = convert.query(GaoLuPredictPO,
                            and_(GaoLuPredictPO.GMT_CREATE > start, GaoLuPredictPO.GMT_CREATE <= end), order_by=order_by)
    elif left == "close" and right == "open":
        ret = convert.query(GaoLuPredictPO,
                            and_(GaoLuPredictPO.GMT_CREATE >= start, GaoLuPredictPO.GMT_CREATE < end), order_by=order_by)
    return ret


def gaolu_history_between_json(start, end, left='close', right='close', order_by=None):
    ret = gaolu_history_between(start, end, left, right, order_by=order_by)
    result = gaolu_schemas.dumps(ret)
    return result


# @transaction(db.session)
def get_gaolu_result_one(query_time):
    start = query_time
    end = query_time + datetime.timedelta(hours=1)
    ret = convert.query(GaoLuPredictPO, GaoLuPredictPO.GMT_CREATE.between(start, end)).first()
    return ret


def gaolu_model_input_one(date, hour):
    ret = convert.query(GaoLuModelInputPO,
                        and_(GaoLuModelInputPO.FDate == date, GaoLuModelInputPO.FHour == hour)).first()
    return ret


def gaolu_insert_predict_result(gaolu_predict_po):
    return add_one(gaolu_predict_po)


def select_simulate_input(query_time):
    if query_time is None:
        query_time = datetime.datetime.now()

    date = query_time.date().strftime("%Y-%m-%d")
    # 0到23格式 更换到 1到24
    hour = query_time.hour + 1
    ret = convert.query(GaoLuModelInputSimulatePO,
                        and_(GaoLuModelInputSimulatePO.FDate == date, GaoLuModelInputSimulatePO.FHour == hour))
    return ret.first()


def insert_input_po(input_po):
    convert.add_one(input_po)


def delete_from_to_predict(start_time, end_time):
    convert.delete_from(GaoLuPredictPO,
                        and_(GaoLuPredictPO.GMT_CREATE > start_time, GaoLuPredictPO.GMT_CREATE < end_time))


def delete_from_to_input(start_time, end_time):
    convert.delete_from(GaoLuModelInputPO,
                        and_(GaoLuModelInputPO.FDate > start_time, GaoLuModelInputPO.FDate < end_time))


def get_latest_input_cache():
    input_po = GaoLuModelInputPO()
    query_time = datetime.datetime.now()
    input_po.GMT_CREATE = query_time
    update_time_dict = redis.hget_all_str_decode('update')
    value_dict = redis.hget_all_str_decode('gaolu')

    for v in input_po.__dir__():
        if v.startswith('CG'):
            if v in update_time_dict:
                time = datetime.datetime.strptime(update_time_dict[v], "%Y-%m-%d %H:%M:%S")
                input_po.FDate = time.strftime("%Y-%m-%d")
                input_po.FHour = time.hour

            input_po.__setattr__(v, float(value_dict[v]) if v in value_dict else None)

    return input_po


def dump_gaolu_data(start: datetime, end: datetime):
    # start_of_day = datetime.datetime(start.year, start.month, start.day) - datetime.timedelta(days=1)
    # hour = start.hour

    time_window_start = end - datetime.timedelta(hours=24)
    time_window_end = end

    chutie = convert.query(gaolu_model.GaoLuChuTie,
                           gaolu_model.GaoLuChuTie.fdatetime.between(time_window_start, time_window_end))\
        .order_by(gaolu_model.GaoLuChuTie.fdatetime.desc()).first()

    if chutie is not None:
        redis.hset("gaolu", "CG_LT_GL_GL04_Ftswd", chutie.ftswd)
        redis.hset("gaolu", "CHUTIE_FBC", chutie.fbc)

    t1_sub_query = convert.query(gaolu_model.ZlJyh, jcxmbm='01048', zfbz='0') \
        .join(gaolu_model.ZlJyt, gaolu_model.ZlJyh.jytid == gaolu_model.ZlJyt.jytid) \
        .filter(gaolu_model.ZlJyt.wlbm == '60110101', func.substring(gaolu_model.ZlJyt.ph, 3, 1) == '4') \
        .group_by(func.substring(gaolu_model.ZlJyt.ph, 1, 7)) \
        .with_entities(func.avg(func.cast(gaolu_model.ZlJyh.jyjg, DECIMAL(10, 5))).label('V'),
                       func.max(func.substring(gaolu_model.ZlJyt.ph, 4, 4)).label('FNumber')) \
        .subquery()
    now_before_48_hour = start - datetime.timedelta(hours=48)
    zl1 = convert.query(gaolu_model.GaoLuChuTie, finsid=8) \
        .join(t1_sub_query, gaolu_model.GaoLuChuTie.fnumber == t1_sub_query.c.FNumber) \
        .filter(gaolu_model.GaoLuChuTie.fdatetime.between(now_before_48_hour, start)) \
        .order_by(gaolu_model.GaoLuChuTie.fdatetime.desc()) \
        .limit(1) \
        .with_entities(t1_sub_query.c.V).first()

    t1_sub_query = convert.query(gaolu_model.ZlJyh, jcxmbm='01058', zfbz='0') \
        .join(gaolu_model.ZlJyt, gaolu_model.ZlJyh.jytid == gaolu_model.ZlJyt.jytid) \
        .filter(gaolu_model.ZlJyt.wlbm == '60110101', func.substring(gaolu_model.ZlJyt.ph, 3, 1) == '4') \
        .group_by(func.substring(gaolu_model.ZlJyt.ph, 1, 7)) \
        .with_entities(func.avg(func.cast(gaolu_model.ZlJyh.jyjg, DECIMAL(10, 5))).label('si_ti'),
                       func.max(func.substring(gaolu_model.ZlJyt.ph, 4, 4)).label('FNumber')) \
        .subquery()

    now_before_48_hour = start - datetime.timedelta(hours=48)
    zl2 = convert.query(gaolu_model.GaoLuChuTie, finsid=8) \
        .join(t1_sub_query, gaolu_model.GaoLuChuTie.fnumber == t1_sub_query.c.FNumber) \
        .filter(gaolu_model.GaoLuChuTie.fdatetime.between(now_before_48_hour, start)) \
        .order_by(gaolu_model.GaoLuChuTie.fdatetime.desc()) \
        .limit(1) \
        .with_entities(t1_sub_query.c.si_ti).first()

    if zl1 is not None:
        logging.info("----> value for V is {}".format(float(zl1.V)))
        redis.hset("gaolu", "CG_LT_GL_GL04_V", float(zl1.V))
    if zl2 is not None:
        logging.info("----> value for V is {}".format(float(zl2.si_ti)))
        redis.hset("gaolu", "CG_LT_GL_GL04_Si_Ti", float(zl2.si_ti))



    liluntie = convert.query(gaolu_model.GaoLuLiLunTie,
                             gaolu_model.GaoLuLiLunTie.fdatetime.between(time_window_start,time_window_end))\
        .order_by(gaolu_model.GaoLuLiLunTie.fdatetime.desc()).first()

    if liluntie is not None:
        redis.hset("gaolu", "CG_LT_GL_GL04_FLiLunTie", liluntie.fliluntie)

    shangliaoqk = convert.query(gaolu_model.ShangLiaoQk,
                             gaolu_model.ShangLiaoQk.fdatetime.between(time_window_start,time_window_end))\
        .order_by(gaolu_model.ShangLiaoQk.fdatetime.desc()).first()
    if shangliaoqk is not None:
        redis.hset("gaolu", "CG_LT_GL_FBatchHour", shangliaoqk.fbatchhour)
        redis.hset("gaolu", "CG_LT_GL_GL04_FCokeRate", shangliaoqk.fcokerate)
        redis.hset("gaolu", "CG_LT_GL_GL04_FCoalRate", shangliaoqk.fcoalrate)

    # 滞后8h的FCokeRate+滞后2h的FCoalRate
    start_1 = start - datetime.timedelta(hours=8)
    day = datetime.datetime(start_1.year, start_1.month, start_1.day)
    hour = start_1.hour
    shangliaoqk_1 = convert.query(gaolu_model.ShangLiaoQk, fdate=day,
                                  fhour=hour, finsid=8).first()

    start_2 = start - datetime.timedelta(hours=2)
    day = datetime.datetime(start_2.year, start_2.month, start_2.day)
    hour = start_2.hour
    shangliaoqk_2 = convert.query(gaolu_model.ShangLiaoQk, fdate=day,
                                  fhour=hour, finsid=8).first()
    if shangliaoqk_1 and shangliaoqk_2:
        redis.hset("gaolu", "CG_LT_GL_GL04_RLB", shangliaoqk_1.fcokerate + shangliaoqk_2.fcoalrate)


def gaolu_insert_banbie_score(gaolu_banbie_score_po):
    return add_one(gaolu_banbie_score_po)


def gaolu_banbie_history_between_query(start, end, left='close', right='close', order_by=None) \
        -> List[GaoLuBanBieScorePO]:
    if left == "close" and right == "close":
        ret = convert.query(GaoLuBanBieScorePO, GaoLuBanBieScorePO.GMT_CREATE.between(start, end), order_by=order_by)
    elif left == "open" and right == "open":
        ret = convert.query(GaoLuBanBieScorePO,
                            and_(GaoLuBanBieScorePO.GMT_CREATE > start, GaoLuBanBieScorePO.GMT_CREATE < end), order_by=order_by)
    elif left == "open" and right == "close":
        ret = convert.query(GaoLuBanBieScorePO,
                            and_(GaoLuBanBieScorePO.GMT_CREATE > start, GaoLuBanBieScorePO.GMT_CREATE <= end), order_by=order_by)
    elif left == "close" and right == "open":
        ret = convert.query(GaoLuBanBieScorePO,
                            and_(GaoLuBanBieScorePO.GMT_CREATE >= start, GaoLuBanBieScorePO.GMT_CREATE < end), order_by=order_by)
    return ret